﻿---------------------------PROCEDURE]
use Mantech_Helpdesk
go
select * from employee
go
create proc sp_ViewStatisticByTypeComp
@C_Type int
as
select c.C_Type, d.D_ID, d.D_Name, count(*) as Total from Complaint c inner join Employee e
on c.E_ID = e.E_ID inner join Department d
on e.D_ID = d.D_ID
where c.C_Type=@C_Type
group by c.C_Type, d.D_ID, d.D_Name
--=====================================================================
--=====================================================================
--=====================================================================
go
-------------------------------PROC LOGIN
create proc sp_CheckLogin
@E_UserName varchar(100),
@E_Password varchar(100)
as
select count(*) from employee where E_UserName=@E_UserName and E_Password=@E_Password and E_Active=1
-------------------------------PROC COMPLAINT
select * from employee
go
create proc sp_DeleteComplaint
@C_ID int
as
update Complaint set C_Active=0 where C_ID=@C_ID
go
create proc sp_GetTopEmPostByEID
@E_ID int
as
select top(1) C_ID from Complaint
where E_ID = @E_ID order by C_ID desc
go
create proc sp_AddNewComplaint
@C_Title nvarchar(max),
@E_ID int,
@C_Type int,
@C_DatePost DateTime,
@C_DeadLine DateTime,
@C_Status bit,
@C_Active bit
as
insert into Complaint values (@C_Title, @E_ID, @C_Type, @C_DatePost, @C_DeadLine, @C_Status, @C_Active)
go
create proc sp_AddNewComplaintDetail
@C_ID int,
@E_ID int,
@CD_Title nvarchar(max),
@CD_Description nvarchar(max),
@CD_DatePost Datetime
as
insert into ComplaintDetail values (@C_ID,@E_ID,@CD_Title,@CD_Description, @CD_DatePost)
go
create proc sp_ReplyComplaint
@C_ID int,
@E_ID int,
@CD_Title nvarchar(max),
@CD_Description nvarchar(max),
@CD_DatePost Datetime,
@C_DeadLine Datetime,
@C_Status bit
as
BEGIN
	insert into ComplaintDetail values (@C_ID,@E_ID,@CD_Title,@CD_Description, @CD_DatePost)
	update Complaint set C_DeadLine=@C_DeadLine, C_Status=@C_Status where C_ID=@C_ID
END
go
create proc sp_GetComplaintByEID
@E_ID int
as
select C_Title, C_type, C_DeadLine, C_ID, E_ID, C_Status, C_Active from Complaint
where E_ID=@E_ID and C_Active=1
group by C_Title, C_type, C_DeadLine, C_ID, E_ID, C_Status, C_Active
go
create proc sp_GetComplaintDetailByCID
@C_ID int
as
select * from ComplaintDetail cd inner join Employee e 
on cd.e_ID = e.E_ID
where cd.C_ID = @C_ID
go
create proc sp_GetAllEmployee
as
select e.e_id, e.e_type ,e.d_id, d.d_name, e.e_username, e.e_password,
	e.e_fullname, e.e_email, e.e_phone, e.e_gender, e.e_image,
	e.e_datejoin, e.e_active from employee e inner join department d
on e.d_id = d.d_id
where e.e_active = 1
go
create proc sp_GetEmployeeByEID
@E_ID int
as
select e.e_id, e.e_type ,e.d_id, d.d_name, e.e_username, e.e_password,
	e.e_fullname, e.e_email, e.e_phone, e.e_gender, e.e_image,
	e.e_datejoin, e.e_active from employee e inner join department d
on e.d_id = d.d_id
where e.e_active = 1 and e.e_ID=@E_ID
-------------------------------PROC ADMIN
/*
	@Action:
		1:Add, 2:Update, 3:Delete
*/
go
CREATE PROCEDURE sp_Admin
	@Action int,
	@ID int,
	@UserName varchar(100),
	@Password varchar(100),
	@FullName nvarchar(100),
	@Email varchar(200),
	@Phone varchar(20),
	@Gender bit,
	@Image ntext,
	@DateJoin datetime,
	@Active bit
AS
BEGIN
	DECLARE @error nvarchar(200)
	SET @error='error'
	IF @Action=1
		BEGIN
			insert into Admin values (@UserName,@Password,@FullName,@Email,@Phone,@Gender,@Image,@DateJoin,@Active)
			IF @@ERROR=0
				SET @error='Insert success a Admin'
		END
	IF @Action=2
		BEGIN
			update Admin set A_FullName=@FullName, A_Email=@Email, A_Phone=@Phone, A_Gender=@Gender, 
					A_Image=@Image, A_DateJoin=@DateJoin, A_Active=@Active where A_ID=@ID
			IF @@ERROR=0
				SET @error='Update success Admin: ' + CONVERT(VARCHAR(10),@ID)
		END
	IF @Action=3
		BEGIN
			update Admin set a_active=0 where A_ID=@ID
			IF @@ERROR=0
				SET @error='Delete success Admin: ' + CONVERT(VARCHAR(10),@ID)
		END
	SELECT @error AS ThongBao
END
-------------------------------PROC DEPARTMENT
/*
	@Action:
		1:Add, 2:Update, 3:Delete
*/
go
CREATE PROCEDURE sp_Department
	@Action int,
	@D_ID int,
	@Name varchar(100),
	@Description nvarchar(max),
	@Active bit
AS
BEGIN
	DECLARE @error nvarchar(200)
	SET @error='error'
	IF @Action=1
		BEGIN
			insert into Department values (@Name,@Description,@Active)
			IF @@ERROR=0
				SET @error='Insert success a DEPARTMENT'
		END
	IF @Action=2
		BEGIN
			update Department set D_Name=@Name, D_Description=@Description, D_Active=@Active where D_ID=@D_ID
			IF @@ERROR=0
				SET @error='Update success DEPARTMENT: ' + CONVERT(VARCHAR(10),@D_ID)
		END
	IF @Action=3
		BEGIN
			UPDATE Department set D_Active=0 where D_ID=@D_ID
			IF @@ERROR=0
				SET @error='Delete success DEPARTMENT: ' + CONVERT(VARCHAR(10),@D_ID)
		END
	SELECT @error AS ThongBao
END
-------------------------------------PROC EMPLOYEE
/*
	@Action:
		1:Add, 2:Update, 3:Delete
*/
go
create proc sp_GetEmployeeByUserName
@E_UserName varchar(100)
as
select * from Employee where E_UserName=@E_UserName and E_Active=1
go
--exec sp_Employee '2','1','1','1','1','1','1','1','1','3333333','2012-4-25','1'
go
create proc sp_DeleteEmployee
@E_ID int
as
update Employee set E_Active=0 where E_ID=@E_ID
go
CREATE PROCEDURE sp_Employee
	@Action int,
	@E_ID int,
	@E_Type int,
	@D_ID int,
	@UserName varchar(100),
	@Password varchar(100),
	@FullName nvarchar(100),
	@Email varchar(200),
	@Phone varchar(20),
	@Gender bit,
	@Image ntext,
	@DateJoin datetime,
	@Active bit
AS
BEGIN
	IF @Action=1
		BEGIN
			insert into Employee values (@E_Type,@D_ID,@UserName,@Password,@FullName,@Email,@Phone,@Gender,@Image,@DateJoin,@Active)
		END
	IF @Action=2
		BEGIN
			update Employee set E_Password=@Password,E_Type=@E_Type, D_ID=@D_ID, E_FullName=@FullName, E_Email=@Email, E_Phone=@Phone, E_Gender=@Gender, 
					E_Image=@Image, E_DateJoin=@DateJoin, E_Active=@Active where E_ID=@E_ID
		END
	IF @Action=3
		BEGIN
			update Employee set E_Active=0 where E_ID=@E_ID
		END
END
------------------------------------PPROC STATISTICS COMPLAINT
/*
	@Action:
		1: View All
		2: View By by since
			Parameter is StartDate, EndDate
*/
-- 
go
CREATE PROCEDURE sp_StatisticComplaint
	@Action int,
	@StartDate DateTime,
	@EndDate DateTime
AS
BEGIN
	IF @Action=1
		BEGIN
			select C_Type, count(*) Total from complaint 
			where C_Active=1
			group by  C_Type
		END
	IF @Action<>1
		BEGIN
			select C_Type, count(*) Total from complaint
			where C_Active=1 and C_DatePost between @StartDate and @EndDate
			group by  C_Type
		END
END
-----------------------------PPROC VIEW COMPLAINT
/*
	@Action:
		1: View All
		2: View By Type
			0=All, 1=Facilities, 2=Wage, 3=Orther isusses
		3: View By Status
			0=All,  1=True, 2=False
		4: View By DeadLine
			1=Gần, 2=Xa
		5: Department by ID
*/
go
--select * from complaint
--select * from department
--select * from employee
--select * from complaintdetail
--exec sp_ViewComplaint '1','1','1','1','2'
CREATE PROCEDURE sp_ViewComplaint
	@Action int,
	@Type int,
	@Status int,
	@DeadLine int,
	@D_ID int
AS
BEGIN
	DECLARE @error nvarchar(200)
	SET @error='error'
	IF @Action=1
		BEGIN
			select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
			inner join Employee e on c.e_ID = e.E_ID
			inner join Department d on e.D_ID = d.D_ID
			where C_Active=1
			group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
		END
	IF @Action=2
		BEGIN
			IF @Type='0'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
			END
			IF @Type<>'0'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Type=@Type and C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
			END
		END
	IF @Action=3
		BEGIN
			IF @Status='0'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
			END
			IF @Status='1'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Status='1' and C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
			END
			IF @Status='2'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Status='0' and C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
			END
		END
	IF @Action=4
		BEGIN
			IF @DeadLine='0'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
				order by C_DeadLine desc
			END
			IF @DeadLine='1'
			BEGIN
				select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
				inner join Employee e on c.e_ID = e.E_ID
				inner join Department d on e.D_ID = d.D_ID
				where C_Active=1
				group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
				order by C_DeadLine asc
			END
		END
	IF @Action=5
		BEGIN
			select c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name from Complaint c
			inner join Employee e on c.e_ID = e.E_ID
			inner join Department d on e.D_ID = d.D_ID
			where C_Active=1 and d.D_ID=@D_ID
			group by c.C_Title, c.C_type, c.C_DeadLine, c.C_ID, c.E_ID, e.E_UserName, c.C_Status, c.C_Active, e.D_ID, e.E_Type, d.D_Name
		END
		SELECT @error AS ThongBao
END



